﻿using Sustainalytics.BusinessResearchProductStates.Entities.ResearchState;
using Sustainalytics.Entities.ProductInvolvement;
using Sustainalytics.Entities.SustainableProducts;
using Sustainalytics.Utils;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Linq;
using static Sustainalytics.ClientDW.Synchronization.SyncProductInvolvement.PISqlDatabase;

namespace Sustainalytics.ClientDW.Synchronization.SyncProductInvolvement
{
    public class PISqlRepository
    {
        private readonly List<ProductInvolvementData> _cddbData;
        private readonly string _storedProcedure;
        private readonly string _indicatorsStoredProcedure;
        private readonly string _solutionIndicatorStoredProcedure;
        private readonly string _storedProcedureCompanyList;

        private readonly PISqlDatabase _database;

        private readonly Dictionary<Guid, PICompanyInternal> _companyDict = new Dictionary<Guid, PICompanyInternal>();
        private readonly List<ProductInvolvementFramework> _indicatorList = new List<ProductInvolvementFramework>();
        private readonly List<Guid> _companiesInProgress = new List<Guid>();
        private readonly List<SustainableProductsFramework> _solutionIndicatorList = new List<SustainableProductsFramework>();

        private PICompanyInternal _currentCompany;
        private ProductInvolvementFrameworkInternal _currentCompanyIndicator;
        private (PIIndicatorsRow piIndicator, SolutionIndicatorRow solutionIndicator) _prevIndicator;
        //TODO - add PIIndicator data according to GA rules

        protected PISqlRepository(List<ProductInvolvementData> cddbData, string storedProcedure, string storedProcedureCompanyList, 
            string indicatorsStoredProcedure, string solutionIndicatorStoredProcedure)
        {
            _cddbData = cddbData;
            _storedProcedure = storedProcedure;
            _indicatorsStoredProcedure = indicatorsStoredProcedure;
            _solutionIndicatorStoredProcedure = solutionIndicatorStoredProcedure;
            _storedProcedureCompanyList = storedProcedureCompanyList;

            var PIConnectionString = ConfigurationManager.ConnectionStrings["PIConnectionString"]?.ConnectionString;

            if (string.IsNullOrEmpty(PIConnectionString))
                throw new ArgumentException("No connection string found for PI!");

            _database = new PISqlDatabase(PIConnectionString, SyncHelper.ReadCommandTimeOutFromConfig());

            ProcessSql();
        }

        public static PISqlRepository GetInstance(List<ProductInvolvementData> cddbData, string storedProcedure, string storedProcedureCompanyList, 
            string indicatorsStoredProcedure, string solutionIndicatorStoredProcedure)
            => new PISqlRepository(cddbData, storedProcedure, storedProcedureCompanyList, indicatorsStoredProcedure, solutionIndicatorStoredProcedure);


        private void GetCurrentCompany(Guid companyId)
        {
            if (!_companyDict.ContainsKey(companyId))
            {
                var cddbCompany = _cddbData.First(x => x.Id == companyId);

                _companyDict.Add(companyId, new PICompanyInternal
                {
                    Id = companyId,
                    CompanyName = cddbCompany.CompanyName,
                    Country = cddbCompany.Country,
                    Industry = cddbCompany.Industry,
                    IndustryId = cddbCompany.IndustryId,
                    PeerGroupName = cddbCompany.PeerGroupName,
                    CompanyId = cddbCompany.CompanyId,
                    Exchange = cddbCompany.Exchange,
                    Ticker = cddbCompany.Ticker,
                    CompanyNameLowerCase = cddbCompany.CompanyNameLowerCase,
                    MarketCap = cddbCompany.MarketCap,
                    Employees = cddbCompany.Employees,
                    CompanyNameSafeUrl = cddbCompany.CompanyNameSafeUrl,
                    Indicators = new Dictionary<(string, string), ProductInvolvementFrameworkInternal>()
                });
            }

            _currentCompany = _companyDict[companyId];
        }

        private void GetCurrentIndicator(string indicatorSymbol, string categorySymbol)
        {
            var key = (indicatorSymbol, categorySymbol);

            if (!_currentCompany.Indicators.ContainsKey(key))
                _currentCompany.Indicators.Add(key, new ProductInvolvementFrameworkInternal
                {
                    IndicatorSymbol = indicatorSymbol,
                    CategorySymbol = categorySymbol,
                    LevelOfInvolvement = PILevelOfInvolvement.FromId(0),
                    Sources = new List<PIDataSource>(),
                    ClientFacingProducts = new List<ClientFacingProduct>(),
                    OwnershipPercent = -1,
                    RangeOwnership = "N/A"
                });

            _currentCompanyIndicator = _currentCompany.Indicators[key];
        }

        private string RRScoreRange(PIIndicatorsRow indicator, SolutionIndicatorRow solutionindicator, int i, int type)
        {
            //used type 1 for range, type 2 for score
            string result = String.Empty;
            if (indicator != null)
            {
                switch (i)
                {
                    case 1: result = type == 1 ? indicator.Revenue_range_1 : indicator.RR1_score.ToString(); break;
                    case 2: result = type == 1 ? indicator.Revenue_range_2 : indicator.RR2_score.ToString(); break;
                    case 3: result = type == 1 ? indicator.Revenue_range_3 : indicator.RR3_score.ToString(); break;
                    case 4: result = type == 1 ? indicator.Revenue_range_4 : indicator.RR4_score.ToString(); break;
                    case 5: result = type == 1 ? indicator.Revenue_range_5 : indicator.RR5_score.ToString(); break;
                }
            }
            else
            {
                switch (i)
                {
                    case 1: result = type == 1 ? solutionindicator.Revenue_range_1 : solutionindicator.RR1_score.ToString(); break;
                    case 2: result = type == 1 ? solutionindicator.Revenue_range_2 : solutionindicator.RR2_score.ToString(); break;
                    case 3: result = type == 1 ? solutionindicator.Revenue_range_3 : solutionindicator.RR3_score.ToString(); break;
                    case 4: result = type == 1 ? solutionindicator.Revenue_range_4 : solutionindicator.RR4_score.ToString(); break;
                    case 5: result = type == 1 ? solutionindicator.Revenue_range_5 : solutionindicator.RR5_score.ToString(); break;
                }
            }
            return result;
        }

        private void CreatePIIndicator(PIIndicatorsRow indicator)
        {
            Dictionary<int, PIRange> revenuerange = new Dictionary<int, PIRange>();
            List<ProductInvolvementCategory> productInvolvementCategories = new List<ProductInvolvementCategory>();

            for (int i = 1; i <= 5; i++)
            {
                revenuerange.Add(i, PIRange.FromRangeAndScore(i, RRScoreRange(indicator, null, i, 1), RRScoreRange(indicator, null, i, 2)));
            }

            ProductInvolvementCategory category = new ProductInvolvementCategory()
            {
                Symbol = indicator.Symbol,
                CategoryOfInvolvement = indicator.Involvement.Trim(),
                RelatedCategorySymbol = indicator.RelatedCategory,
                Description = indicator.Description.Trim(),
                OwnershipYesNo = indicator.Ownership_Yes_No == "Yes",
                RevenueRangeYesNo = indicator.Revenue_range_Yes_No == "Yes",
                RevenueRange = revenuerange,
                Score = (indicator.Ownership_Yes_No == "No" && indicator.Revenue_range_Yes_No == "No") ? indicator.Score.Value : (indicator.Ownership_Score ?? -1)
            };
            productInvolvementCategories.Add(category);

            if (_prevIndicator.piIndicator == null || _prevIndicator.piIndicator.IndicatorSymbol != indicator.IndicatorSymbol)
            {
                var pi = new ProductInvolvementFramework()
                {
                    Id = Guid.NewGuid(),
                    Symbol = indicator.IndicatorSymbol,
                    Name = indicator.Name,
                    Categories = productInvolvementCategories
                };
                _indicatorList.Add(pi);
            }
            else
            {
                var pi = _indicatorList.FirstOrDefault(a => a.Symbol == indicator.IndicatorSymbol);
                pi.Categories.Add(category);
            }
            _prevIndicator.piIndicator = indicator;
        }

        private void CreateSolutionIndicator(SolutionIndicatorRow indicator)
        {
            Dictionary<int, PIRange> revenuerange = new Dictionary<int, PIRange>();
            List<SustainableProductsCategory> sustainableCategories = new List<SustainableProductsCategory>();

            for (int i = 1; i <= 5; i++)
            {
                revenuerange.Add(i, PIRange.FromRangeAndScore(i, RRScoreRange(null, indicator, i, 1), RRScoreRange(null, indicator, i, 2)));
            }

            SustainableProductsCategory category = new SustainableProductsCategory()
            {
                Symbol = indicator.Symbol,
                CategoryOfInvolvement = indicator.Involvement.Trim(),
                RelatedCategorySymbol = indicator.RelatedCategory,
                Description = indicator.Description.Trim(),
                RevenueRange = revenuerange
            };
            sustainableCategories.Add(category);


            if (_prevIndicator.solutionIndicator == null || _prevIndicator.solutionIndicator.IndicatorSymbol != indicator.IndicatorSymbol)
            {
                var pi = new SustainableProductsFramework()
                {
                    Id = Guid.NewGuid(),
                    Symbol = indicator.IndicatorSymbol,
                    Name = indicator.Name,
                    Categories = sustainableCategories
                };
                _solutionIndicatorList.Add(pi);
            }
            else
            {
                var pi = _solutionIndicatorList.FirstOrDefault(a => a.Symbol == indicator.IndicatorSymbol);
                pi.Categories.Add(category);
            }
            _prevIndicator.solutionIndicator = indicator;
        }

        private void GetCurrentEntities(Guid companyId, string indicatorSymbol, string categorySymbol)
        {
            GetCurrentCompany(companyId);

            GetCurrentIndicator(indicatorSymbol, categorySymbol);
        }

        private void ProcessRevenueRangeId(string value)
        {
            if (!Int32.TryParse(value, out int parsedRangeId))
                return;

            _currentCompanyIndicator.LevelOfInvolvement = PILevelOfInvolvement.FromId(parsedRangeId);

            var start = _currentCompanyIndicator.LevelOfInvolvement.Start;
            var stop = _currentCompanyIndicator.LevelOfInvolvement.Stop;

            if (!stop.NearlyEq(100) && (stop % 1).NearlyEq(0))
                stop -= 0.1;

            if (!start.NearlyEq(-1.00) && !stop.NearlyEq(-1.00))
                _currentCompanyIndicator.RangeOwnership = $"{start.ToString("G", CultureInfo.InvariantCulture)}-{stop.ToString("G", CultureInfo.InvariantCulture)}%";
        }


        private void ProcessOwnershipPercent(string value)
        {
            if (!Double.TryParse(value, out double parsedPercent))
                return;

            _currentCompanyIndicator.OwnershipPercent = parsedPercent;

            if (parsedPercent <= 0)
                return;

            _currentCompanyIndicator.RangeOwnership = String.Format("{0:N2}%", parsedPercent);
        }

        private void ProcessPlatformStatusId(string value)
        {
            //id  Status
            //0   Unavailable
            //1   Available
            //2   DS only
            //3   GA only

            if (!Int32.TryParse(value, out int parsedPlatformStatus))
                return;

            switch (parsedPlatformStatus)
            {
                case 1:
                    _currentCompanyIndicator.ClientFacingProducts.Add(ClientFacingProduct.GA);
                    _currentCompanyIndicator.ClientFacingProducts.Add(ClientFacingProduct.DS);
                    break;
                case 2:
                    _currentCompanyIndicator.ClientFacingProducts.Add(ClientFacingProduct.DS);
                    break;
                case 3:
                    _currentCompanyIndicator.ClientFacingProducts.Add(ClientFacingProduct.GA);
                    break;
            }
        }

        private void ProcessScore(string value)
        {
            if (!Int32.TryParse(value, out int parsedScore))
                return;

            _currentCompanyIndicator.Score = parsedScore;
        }

        private void ProcessRevenuePercentage(string value)
        {
            if (!Decimal.TryParse(value, out decimal parsedRevenuePercentage))
                return;

            _currentCompanyIndicator.RevenuePercentage = parsedRevenuePercentage;
        }

        private void ProcessProductRevenue(string value)
        {
            if (!Decimal.TryParse(value, out decimal parsedRevenue))
                return;

            _currentCompanyIndicator.ProductRevenue = parsedRevenue;
        }

        private void ProcessFinancialYear(string value)
        {
            if (!Int32.TryParse(value, out int financialYear))
                return;

            _currentCompanyIndicator.FinancialYear = financialYear;
        }

        private void ProcessLastEdited(string value)
        {
            if (DateTime.TryParseExact(value, "yyyy-MM-dd HH:mm:ss.000", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime parsedDateTime))
                _currentCompanyIndicator.LastEdited = parsedDateTime;
        }

        private void ProcessSourceType(string value, int index)
        {
            if (Enum.TryParse(value, true, out PIDataSourceType parsedSourceType))
                if (!_currentCompanyIndicator.Sources.IsNullOrEmpty() && index < _currentCompanyIndicator.Sources.Count)
                {
                    _currentCompanyIndicator.Sources.ElementAt(index).Type = parsedSourceType;
                }
                else
                {
                    _currentCompanyIndicator.Sources.Add(new PIDataSource { Type = parsedSourceType });
                }
        }

        private void ProcessSourceName(string value, int index)
        {
            if (!_currentCompanyIndicator.Sources.IsNullOrEmpty() && index < _currentCompanyIndicator.Sources.Count)
            {
                _currentCompanyIndicator.Sources.ElementAt(index).Name = value;
            }
            else
            {
                _currentCompanyIndicator.Sources.Add(new PIDataSource { Name = value });
            }
        }

        private void ProcessField(string key, string value)
        {
            switch (key)
            {
                case "Category of Involvement Type":
                    _currentCompanyIndicator.CategoryOfInvolvementType = value;
                    break;
                case "Revenue Range YesNo":
                    _currentCompanyIndicator.HasLevelOfInvolvement = value == "Yes";
                    break;
                case "Revenue Range.RangeId":
                    ProcessRevenueRangeId(value);
                    break;
                case "Ownership YesNo":
                    _currentCompanyIndicator.HasOwnershipPercent = value == "Yes";
                    break;
                case "Ownership Percent":
                    ProcessOwnershipPercent(value);
                    break;
                case "Standard Analysis":
                    _currentCompanyIndicator.StandardAnalysis = value;
                    break;
                case "Source.Name.1":
                    ProcessSourceName(value, 0);
                    break;
                case "Source.Type.1":
                    ProcessSourceType(value, 0);
                    break;
                case "Source.Name.2":
                    ProcessSourceName(value, 1);
                    break;
                case "Source.Type.2":
                    ProcessSourceType(value, 1);
                    break;
                case "Source.Name.3":
                    ProcessSourceName(value, 2);
                    break;
                case "Source.Type.3":
                    ProcessSourceType(value, 2);
                    break;
                case "Source.Name.4":
                    ProcessSourceName(value, 3);
                    break;
                case "Source.Type.4":
                    ProcessSourceType(value, 3);
                    break;
                case "Last Edited":
                    ProcessLastEdited(value);
                    break;
                case "Score":
                    ProcessScore(value);
                    break;
                case "Platform Status.id":
                    ProcessPlatformStatusId(value);
                    break;
                case "Product Involvement Indicator":
                    _currentCompanyIndicator.IndicatorSymbolDescription = value;
                    break;
                case "Description":
                    _currentCompanyIndicator.CategoryOfInvolvementDescription = value;
                    break;
                case "Revenue Percentage":
                    ProcessRevenuePercentage(value);
                    break;
                case "Product Revenue":
                    ProcessProductRevenue(value);
                    break;
                case "Currency":
                    _currentCompany.Currency = value;
                    break;
                case "Product Involvement Analysis":
                    _currentCompanyIndicator.ProductInvolvementAnalysis = value;
                    break;
                case "Revenue Analysis":
                    _currentCompanyIndicator.RevenueAnalysis = value;
                    break;
                case "Revenue Analysis Source":
                    _currentCompanyIndicator.RevenueAnalysisSource = value;
                    break;
                case "FinancialYear":
                    ProcessFinancialYear(value);
                    break;
            }
        }

        private void ProcessSql()
        {
            if (_indicatorsStoredProcedure != null)
            {
                var sqlIndicatorData = _database.GetIndicatorData(_indicatorsStoredProcedure);

                for (int i = 0; i < sqlIndicatorData.Count; i++)
                {
                    CreatePIIndicator(sqlIndicatorData[i]);
                }
            }
            else
            {
                var sqlSolutionIndicatorData = _database.GetSolutionData(_solutionIndicatorStoredProcedure);

                for (int i = 0; i < sqlSolutionIndicatorData.Count; i++)
                {
                    CreateSolutionIndicator(sqlSolutionIndicatorData[i]);
                }
            }
            var sqlData = _database.GetData(_storedProcedure);

            for (int i = 0; i < sqlData.Count; i++)
            {
                if (!_cddbData.Any(x => x.Id == sqlData[i].CompanyId))
                    continue;

                GetCurrentEntities(sqlData[i].CompanyId, sqlData[i].IndicatorSymbol, sqlData[i].CategorySymbol);

                ProcessField(sqlData[i].FieldKey, sqlData[i].FieldValue);
            }

            var sqlCompanyIds = _database.GetCompanyIds(_storedProcedureCompanyList);

            var cddbverifiedcompanies = sqlCompanyIds.Where(x => _cddbData.Select(a=>a.Id).Contains(x.CompanyId)).ToList();

            var cddbnotverifiedcompanies = _cddbData.Where(x => !cddbverifiedcompanies.Select(a=>a.CompanyId).Contains(x.Id)).ToList();

            _companiesInProgress.AddRange(cddbnotverifiedcompanies.Select(a=>a.Id));

            var noCompanies = cddbverifiedcompanies.Where(x => !sqlData.Select(a => a.CompanyId).Contains(x.CompanyId)).ToList();

            for (int i = 0; i < noCompanies.Count; i++)
            {
                GetCurrentCompany(noCompanies[i].CompanyId);
            }
        }

        public (List<Guid> CompaniesInProgress, List<ProductInvolvementData> AssesmentList, List<ProductInvolvementFramework> IndicatorList) GetTraditionalData()
        {
            var companiesInProgress = _companiesInProgress.Select(x => x).ToList();
            var companyAssessmentList = _companyDict.Select(x => x.Value).ToList().ConvertAll(x => x.ToPICompanyAssessment());
            var indicatorList = _indicatorList.Select(x => x).ToList();
            return (companiesInProgress, companyAssessmentList, indicatorList);
        }

        public (List<Guid> CompaniesInProgress, List<SustainableProductsData> SolutionList, List<SustainableProductsFramework> SolutionIndicatorList) GetSolutionsData()
        {
            var companiesInProgress = _companiesInProgress.Select(x => x).ToList();
            var companySolutionList = _companyDict.Select(x => x.Value).ToList().ConvertAll(x => x.ToPICompanySolution());
            var solutionIndicatorList = _solutionIndicatorList.Select(x => x).ToList();
            return (companiesInProgress, companySolutionList, solutionIndicatorList);
        }
    }
}
